#! /usr/bin/env python
# -*- coding: utf-8 -*-
from dbutils.persistent_db import PersistentDB
import pymysql


def select():
    # 创建连接
    conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd='root123456', db='test', charset='utf8mb4')

    # 创建游标(查询数据返回为元组格式)
    cursor = conn.cursor()
    try:
        cursor.execute("select * from sys_user_info")
        results = cursor.fetchall()
        # print(results)
        for row in results:
            # print(row)
            first_name = row[0]
            last_name = row[1]
            age = row[2]
            sex = row[3]
            income = row[4]
            print(first_name, last_name, age, sex, income)
    except:
        print('Uable to fetch data!')
    conn.commit()
    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()


POOL = PersistentDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxusage=None,  # 一个链接最多被重复使用的次数,None表示无限制
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端,检查是否服务可用。
    # 如：0 = None = never,
    # 1 = default = whenever it is requested,
    # 2 = when a cursor is created,
    # 4 = when a query is executed,
    # 7 = always
    closeable=False,
    # 如果为False时, conn.close() 实际上被忽略,供下次使用,再线程关闭时,才会自动关闭链接。
    # 如果为True时, conn.close()则关闭链接,那么再次调用pool.connection时就会报错,因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
    threadlocal=None,  # 本线程独享值得对象,用于保存链接对象,如果链接对象被重置
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123456',
    database='test',
    charset='utf8',
)


def connect():
    # 创建连接
    # conn = pymysql.connect(host='192.168.11.38', port=3306, user='root', passwd='apNXgF6RDitFtDQx', db='m2day03db')
    conn = POOL.connection()
    # 创建游标
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    return conn, cursor


def close(conn, cursor):
    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()


def fetch_one(sql, args):
    conn, cursor = connect()
    # 执行SQL，并返回收影响行数
    effect_row = cursor.execute(sql, args)
    result = cursor.fetchone()
    close(conn, cursor)
    return result


def fetch_all(sql, args):
    conn, cursor = connect()
    # 执行SQL，并返回收影响行数
    cursor.execute(sql, args)
    result = cursor.fetchall()
    close(conn, cursor)
    return result


def insert(sql, args):
    """
    创建数据
    :param sql: 含有占位符的SQL
    :return:
    """
    conn, cursor = connect()
    # 执行SQL，并返回收影响行数
    effect_row = cursor.execute(sql, args)
    conn.commit()
    close(conn, cursor)


def delete(sql, args):
    """
    创建数据
    :param sql: 含有占位符的SQL
    :return:
    """
    conn, cursor = connect()
    # 执行SQL，并返回收影响行数
    effect_row = cursor.execute(sql, args)
    conn.commit()
    close(conn, cursor)
    return effect_row


def update(sql, args):
    conn, cursor = connect()
    # 执行SQL，并返回收影响行数
    effect_row = cursor.execute(sql, args)
    conn.commit()
    close(conn, cursor)
    return effect_row


if __name__ == '__main__':
    select()
